var _Query = function () {
  let wopts = {bookType: 'xlsx', bookSST: false, type: 'binary'};//这里的数据是用来定义导出的格式类型
  let dataType = DataUtils.get("dataType");
  let certTypeMap = DataUtils.get("certTypeMap");
  let myData = {
    certData:{},dataSource:{}
  };
  function init(cb) {

    myData.certData = CertData.getCertData();
    myData.dataSource = CertData.getData();
    cb&& cb();
  }
  function getAwardsDetail() {
    return myData.certData;
  }

  let awardColumns = ["UserID", "Name", "FirstName", "LastName", "StudentNumber", "TeamNumber", "SchoolName", "SchoolRegion", "HSV", "Division", "TestName", "Ranking","Score","StudentType"];
  let columns = ["UserID", "Name", "FirstName", "LastName", "StudentNumber", "TeamNumber", "SchoolName", "SchoolRegion", "HSV", "Division",
      "StudentScore1", "StudentScore2", "StudentScore3", "StudentScore4", "StudentScore5",
      "StudentScore6", "StudentScore7", "StudentScore8", "StudentScore9", "StudentScore10",
      "StudentScore", "TeamScore", "SQScore", "HKSScore1", "HKSScore2", "HKSScore"];

  let awardNamesAll = {
    "usad":{
      "Classic":[
        "c1", //全能-全国-个人-学科单项奖-Music
        "c2", //全能-全国-个人-学科单项奖-Economics
        "c3", //全能-全国-个人-学科单项奖-Social Science
        "c4", //全能-全国-个人-学科单项奖-Mathematics
        "c5", //全能-全国-个人-学科单项奖-Literature
        "c6", //全能-全国-个人-学科单项奖-Science
        "c7", //全能-全国-个人-学科单项奖-Art
        "c8", //全能-全国-个人-学科单项奖-Speech
        "c9", //全能-全国-个人-学科单项奖-Interview
        "c10", //全能-全国-个人-学科单项奖-Essay
        "c11", //全能-全国-个人-个人总分奖
        "c13", //全能-全国-团队-团队总分奖
        "d113", //全能-全国-团队-骇客松总分奖
        "c15", //全能-全国-团队-SQ团队奖
        "c12", //全能-全国-个人-队内最高分
        "cc3", //全能-全国-团队-最佳新秀奖团队奖
        "cc4", //全能-全国-个人-最佳新秀奖个人奖
        "d101", //全能-全国-个人-全场第一-Music
        "d102", //全能-全国-个人-全场第一-Economics
        "d103", //全能-全国-个人-全场第一-Social Science
        "d104", //全能-全国-个人-全场第一-Mathematics
        "d105", //全能-全国-个人-全场第一-Literature
        "d106", //全能-全国-个人-全场第一-Science
        "d107", //全能-全国-个人-全场第一-Art
        "d108", //全能-全国-个人-全场第一-Speech
        "d109", //全能-全国-个人-全场第一-Interview
        "d110", //全能-全国-个人-全场第一-Essay
        "d111", //全能-全国-个人-全场第一-全能模式个人总分
      ],
      "Seed":[
        "seed1", //usad种子-全国-个人-学科单项奖-Music
        "seed2", //usad种子-全国-个人-学科单项奖-Economics
        "seed3", //usad种子-全国-个人-学科单项奖-Social Science
        "seed4", //usad种子-全国-个人-学科单项奖-Mathematics
        "seed5", //usad种子-全国-个人-学科单项奖-Literature
        "seed6", //usad种子-全国-个人-学科单项奖-Science
        "seed7", //usad种子-全国-个人-学科单项奖-Art
        "seed8", //usad种子-全国-个人-学科单项奖-Speech
        "seed9", //usad种子-全国-个人-学科单项奖-Interview
        "seed10", //usad种子-全国-个人-学科单项奖-Essay
        "seed11", //usad种子-全国-个人-个人总分奖
        "seed12", //usad种子-全国-个人-队内最高分
        "seed18", //usad种子-全国-团队-骇客松总分奖
        "seed14", //usad种子-全国-团队-SQ团队奖
        "seed13", //usad种子-全国-团队-团队总分奖
        "seed20", //usad种子-全国-团队-最佳新秀奖团队奖
        "seed19", //usad种子-全国-个人-最佳新秀奖个人奖
        "d101", //全能-全国-个人-全场第一-Music
        "d102", //全能-全国-个人-全场第一-Economics
        "d103", //全能-全国-个人-全场第一-Social Science
        "d104", //全能-全国-个人-全场第一-Mathematics
        "d105", //全能-全国-个人-全场第一-Literature
        "d106", //全能-全国-个人-全场第一-Science
        "d107", //全能-全国-个人-全场第一-Art
        "d108", //全能-全国-个人-全场第一-Speech
        "d109", //全能-全国-个人-全场第一-Interview
        "d110", //全能-全国-个人-全场第一-Essay
        "d112", //全能-全国-个人-全场第一-种子模式个人总分
        "seed.ex.01", // 种子-全国-个人-种子突破奖(音乐)
        "seed.ex.02", // 种子-全国-个人-种子突破奖(经济)
        "seed.ex.03", // 种子-全国-个人-种子突破奖(社会科学)
        "seed.ex.04", // 种子-全国-个人-种子突破奖(数学)
        "seed.ex.05", // 种子-全国-个人-种子突破奖(文学)
        "seed.ex.06", // 种子-全国-个人-种子突破奖(科学)
        "seed.ex.07", // 种子-全国-个人-种子突破奖(艺术)
        "seed.ex.08", // 种子-全国-个人-种子突破奖(演讲)
        "seed.ex.09", // 种子-全国-个人-种子突破奖(面试)
        "seed.ex.10", // 种子-全国-个人-种子突破奖(写作)
      ]
    },
    "usap":{
      "Classic":[
        "c22", //全国-个人-学科单项奖-Literature
        "c23", //全国-个人-学科单项奖-Science
        "c24", //全国-个人-学科单项奖-Fine Arts
        "c25", //全国-个人-学科单项奖-Mathematics
        "c26", //全国-个人-学科单项奖-Social Science
        "c27", //全国-个人-个人总分奖
        "c29", //全国-团队-团队总分奖
        "usap.d8", //全国-团队-骇客松作品奖
        "usap.d9", //全国-团队-骇客松展示奖
        "c32", //全国-团队-SQ团队奖
        "c28", //全国-个人-队内最高分
        "usap.d1", //全国-个人-写作优胜奖
        "cc1", //全国-团队-最佳新秀奖团队奖
        "cc2", //全国-个人-最佳新秀奖个人奖
        "usap.d2", //全国-个人-全场第一-Literature
        "usap.d3", //全国-个人-全场第一-Science
        "usap.d4", //全国-个人-全场第一-Fine Arts
        "usap.d5", //全国-个人-全场第一-Mathematics
        "usap.d6", //全国-个人-全场第一-Social Science
        "usap.d7", //全国-个人-全场第一-个人总分奖

      ]
    }
  };

  let awardNamesRegion = {
    "usad":{
      "Classic":[
        "area101", //全能-区域-个人-学科单项奖-Music
        "area102", //全能-区域-个人-学科单项奖-Economics
        "area103", //全能-区域-个人-学科单项奖-Social Science
        "area104", //全能-区域-个人-学科单项奖-Mathematics
        "area105", //全能-区域-个人-学科单项奖-Literature
        "area106", //全能-区域-个人-学科单项奖-Science
        "area107", //全能-区域-个人-学科单项奖-Art
        "area108", //全能-区域-个人-学科单项奖-Speech
        "area109", //全能-区域-个人-学科单项奖-Interview
        "area110", //全能-区域-个人-学科单项奖-Essay
        "area111", //全能-区域-个人-个人总分奖
        "area112", //全能-区域-团队-团队总分奖
      ],
      "Seed":[
        "area113", //usad种子-区域-个人-学科单项奖-Music
        "area114", //usad种子-区域-个人-学科单项奖-Economics
        "area115", //usad种子-区域-个人-学科单项奖-Social Science
        "area116", //usad种子-区域-个人-学科单项奖-Mathematics
        "area117", //usad种子-区域-个人-学科单项奖-Literature
        "area118", //usad种子-区域-个人-学科单项奖-Science
        "area119", //usad种子-区域-个人-学科单项奖-Art
        "area120", //usad种子-区域-个人-学科单项奖-Speech
        "area121", //usad种子-区域-个人-学科单项奖-Interview
        "area122", //usad种子-区域-个人-学科单项奖-Essay
        "area123", //usad种子-区域-个人-个人总分奖
        "area124", //usad种子-区域-团队-团队总分奖
      ]
    },
    "usap":{
      "Classic":[
        "usap.area101", //区域-个人-学科单项奖-Literature
        "usap.area102", //区域-个人-学科单项奖-Science
        "usap.area103", //区域-个人-学科单项奖-Fine Arts
        "usap.area104", //区域-个人-学科单项奖-Mathematics
        "usap.area105", //区域-个人-学科单项奖-Social Science
        "usap.area111", //区域-个人-个人总分奖
        "usap.area112", //区域-团队-团队总分奖
      ]
    }
  };

  if (dataType == "usap"){
    columns = ["UserID", "Name", "FirstName", "LastName", "StudentNumber"
      , "TeamNumber", "SchoolName", "SchoolRegion", "HSV", "Division", "StudentScore1", "StudentScore2", "StudentScore3"
      , "StudentScore4", "StudentScore5","StudentScore6", "StudentScore", "TeamScore", "SQScore",  "HKSCore1", "HKSCore2", "HKSCore"];
  }
  function getAwardNames(area,mode) {
    let names = area =="all" ? awardNamesAll:awardNamesRegion;
    names = names[dataType];
    return mode ? names[mode] : names;

  }
  function filterAwardData(excelData) {
    let awardData = {};
    $.each(excelData, (k, v) => {
      let list = [];
      (v||[]).forEach(g=>{
        (g["list"]||[]).forEach(item =>{
          let newItem = {};
          awardColumns.forEach(cName => newItem[cName] = item[cName]);
          newItem["Award"] = g["award"];
          list.push(newItem);
        });
      });

      awardData[k] = list;
    });
    return awardData
  }

  function createExcel(wb, fileName) {


    var data = new File([s2ab(XLSX.write(wb, wopts))]
      , fileName, {type: 'text/plain;charset=utf-8'});
    upload("/upload", {"name": data.name, data}, (r) => {
      layer.msg("成功下载，路径：" + r["data"])
    }, (r) => {
      layer.msg("下载失败")
    })
  }

  function s2ab(s) {
    if (typeof ArrayBuffer !== 'undefined') {
      var buf = new ArrayBuffer(s.length);
      var view = new Uint8Array(buf);
      for (var i = 0; i != s.length; ++i)
        view[i] = s.charCodeAt(i) & 0xFF;
      return buf;
    } else {
      var buf = new Array(s.length);
      for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
      return buf;
    }

  }

  function getStudentAwardsMap(awardMap) {
    return CertData.getStudentAwardsMap();
  }

  function genAwards() {
    let excelData = getAwardsDetail();
    let awardData = filterAwardData(excelData);
    const wb = {SheetNames: [], Sheets: {}, Props: {}};
    $.each(awardData, (k, v) => {
      wb.SheetNames.push(k);
      try {
        wb.Sheets[k] = XLSX.utils.json_to_sheet(v);
      } catch (e) {
        debugger;
      }

    });
    createExcel(wb, "query/全国及区域获奖清单." + (new Date()).Format("yyyyMMddhhmmss") + '.xlsx')
  }

  function getStudentData(awardFullMap,mode) {
    let data = myData.dataSource;
    let list = data[ mode+".student"];
    let excelDataList = [];
    let awardMap = getStudentAwardsMap(awardFullMap);

    list.forEach(item => {

      let newItem = {};
      columns.forEach(c => {
        if (item[c]){
          newItem[c] = item[c];
        }else{
          newItem[c] = "";
        }

      });
      let awardRegion = awardMap["region"][item["StudentNumber"]];
      let regInfo = attachAwardInfo(newItem, getAwardNames("area",mode), awardRegion, "");
      newItem["RegNum"] = regInfo.num;
      newItem["RegIsOK"] = regInfo.isOk;

      let awardAll = awardMap["all"][item["StudentNumber"]];
      let allInfo = attachAwardInfo(newItem, getAwardNames("all",mode), awardAll, "");
      newItem["AllNum"] = allInfo.num;
      newItem["AllIsOK"] = allInfo.isOk;

      newItem["TotalNum"] = regInfo.num + allInfo.num;
      newItem["TotalIsOK"] = regInfo.isOk || regInfo.isOk

      excelDataList.push(newItem);
    });
    return excelDataList;
  }

  function getSchoolData(studentData) {
    let result = [];
    let regionList = studentData.groupBy(["SchoolRegion"]);
    let startRow = 2;
    $.each(regionList, (k0, v0) => {
      let num1 = 0, num2 = 0, num3 = 0, num4 = 0, num5 = 0, num6 = 0, num7 = 0;
      let schoolList = v0["items"].groupBy(["SchoolName"]);
      $.each(schoolList, (k, v) => {
        let studentNum = 0;
        let awardNumRegion = 0;
        let awardNumAll = 0;
        let awardNumTotal = 0;
        let awardIsOKNumRegion = 0;
        let awardIsOkNumAll = 0;
        let awardIsOkTotal = 0;
        $.each(v["items"], (k2, v2) => {
          studentNum++;
          awardNumRegion += v2["RegNum"];
          awardIsOKNumRegion += v2["RegIsOK"];
          awardNumAll += v2["AllNum"];
          awardIsOkNumAll += v2["AllIsOK"];
          awardNumTotal += v2["TotalNum"];
          awardIsOkTotal += v2["TotalIsOK"];
        });
        let newItem = [];
        newItem.push(v0["key"]["SchoolRegion"], v["key"]["SchoolName"], studentNum
          , awardNumRegion, awardIsOKNumRegion, awardNumAll, awardIsOkNumAll, awardNumTotal, awardIsOkTotal
        );
        result.push(newItem);

        num1 += studentNum;
        num2 += awardNumRegion;
        num3 += awardIsOKNumRegion;
        num4 += awardNumAll;
        num5 += awardIsOkNumAll;
        num6 += awardNumTotal;
        num7 += awardIsOkTotal;
      });
      let endRow = startRow + schoolList.length - 1;
      let newSumItem = [v0["key"]["SchoolRegion"] + "汇总", "", num1, num2, num3, num4, num5, num6, num7];
      result.push({
        'data': newSumItem,
        "merge": [{a: "A" + startRow, b: "A" + endRow}, {a: "A" + (endRow + 1), b: "B" + (endRow + 1)}],
        "style": JSON.stringify({
          "fill": {"type": "pattern", "color": ["#9CC3E8"], "pattern": 1},
          "font": {"bold": true}
        })
      });
      startRow = endRow + 2;
    });

    return result;
  }

  function attachAwardInfo(item, names, awardMap, pre) {
    let isOk = 0;
    let num = 0;
    names.forEach(col => {
      if (awardMap && awardMap[col]) {
        isOk = 1;
        num++;
        item[pre + col] = awardMap[col];

      } else {
        item[pre + col] = "";
      }
    });

    return {isOk, num}
  }

  function genStudentExcel(mode) {
    let awardFullMap = getAwardsDetail();
    let list = getStudentData(awardFullMap,mode);
    let excelData = [];
    list.forEach(item => {
      let newItem = [];
      $.each(item, (k, v) => newItem.push(v))
      excelData.push(newItem);
    });

    let template = "html/"+dataType+"/query/4.2."+dataType+(mode?`.${mode}`:"")+".xlsx";
    $$.op("report", "excel", {
      template,
      excel: [{"sheet": "student", "data": excelData, "startRow": 3}],
      name: "query/个人获奖情况."+mode+"." + (new Date()).Format("yyyyMMddhhmmss") + ".xlsx"
    }, (r) => {
      layer.msg("成功下载，路径：" + r)
    }, (r) => {
      layer.msg("下载失败")
    });
  }

  function genSchoolExcel(mode) {
    let awardFullMap = getAwardsDetail();

    let list = getStudentData(awardFullMap,mode);

    let excelData = [];
    list.forEach(item => {
      let newItem = [];
      $.each(item, (k, v) => newItem.push(v));
      excelData.push(newItem);
    });
    let schoolData = getSchoolData(list);

    let template = "html/"+dataType+"/query/4.3."+dataType+(mode?`.${mode}`:"")+".xlsx";
    $$.op("report", "excel", {
      template,
      excel: [
        {"sheet": "3 中国赛成绩排名原始数据", "data": excelData, "startRow": 3},
        {"sheet": "2 各校得奖情况", "data": schoolData, "startRow": 2}
      ],
      name: "query/全国奖与区域奖对比分析."+mode+"." + (new Date()).Format("yyyyMMddhhmmss") + ".xlsx"
    }, (r) => {
      layer.msg("成功下载，路径：" + r)
    }, (r) => {
      layer.msg("下载失败")
    });
  }

  function genTeam(mode){
    let name = mode+".team"
    let list = CertData.getCertListByName(name, "TeamScore", 10000);

    const wb = {SheetNames: [], Sheets: {}, Props: {}};
    wb.SheetNames.push("团队总分排名");
    try {
      wb.Sheets["团队总分排名"] = XLSX.utils.json_to_sheet(list);
    } catch (e) {
      console.error(e);
      debugger;
    }
    createExcel(wb, "query/团队总分排名(全场)."+mode+"." + (new Date()).Format("yyyyMMddhhmmss") + '.xlsx')
  }

  init();
  return {
    // getAwardsDetail,
    genAwards,
    genStudentExcel,
    genSchoolExcel,
    genTeam
  }
}();
